package model;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

public class ProductDAO {
	private static final String jndi = "java:comp/env/jdbc/struts";
	private DataSource ds = null;

	public ProductDAO() {
		try {
			Context ctx = new InitialContext();
			ds = (DataSource) ctx.lookup(jndi);
		} catch (NamingException e) {
			e.printStackTrace();
		}
	}

	private static final String SELECT_SQL = "select ID , NAME , PRICE , MAKE , EXPIRE   from product where id = ?";

	public ProductBean select(int id) {
		Connection conn = null;
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		ProductBean result = null;

		try {
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(SELECT_SQL);
			pstmt.setInt(1, id);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				result = new ProductBean();
				result.setId(rs.getInt("ID"));
				result.setName(rs.getString("NAME"));
				result.setPrice(rs.getDouble("PRICE"));
				result.setMake(rs.getDate("MAKE"));
				result.setExpire(rs.getInt("EXPIRE"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
			}
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

		return result;
	}

	private static final String INSERT_BY_ID = "insert into product (ID) values (?)";

	public ProductBean insert(int id) {
		ProductBean result = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(INSERT_BY_ID);
			pstmt.setInt(1, id);
			int i = pstmt.executeUpdate();
			if (i == 1) {
				result = this.select(id);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

		return result;
	}

	private static final String INSERT = "insert into product (ID , NAME , PRICE , MAKE , EXPIRE ) values (?, ? , ? ,?, ? ) ";

	public ProductBean insert(ProductBean bean) {
		ProductBean result = null;
		Connection conn = null;
		PreparedStatement pstmt = null;

		try {
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(INSERT);
			pstmt.setInt(1, bean.getId());
			pstmt.setString(2, bean.getName());
			pstmt.setDouble(3, bean.getPrice());
			java.util.Date make = bean.getMake();
			if(make!=null) {
				long time = make.getTime();
				pstmt.setDate(4, new java.sql.Date(time));
			} else {
				pstmt.setDate(4, null);
			}
			pstmt.setInt(5, bean.getExpire());

			int i = pstmt.executeUpdate();
			if (i == 1) {
				result = this.select(bean.getId());
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

		return result;
	}

	private static final String UPDATE = "update product set NAME =? , PRICE=? , MAKE=? , EXPIRE=? where ID=?";

	public ProductBean update(ProductBean bean) {
		ProductBean result = null;
		Connection conn = null;
		PreparedStatement pstmt = null;

		try {
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(UPDATE);
			pstmt.setString(1, bean.getName());
			pstmt.setDouble(2, bean.getPrice());
			java.util.Date make = bean.getMake();
			if(make!=null) {
				long time = make.getTime();
				pstmt.setDate(3, new java.sql.Date(time));
			} else {
				pstmt.setDate(3, null);
			}
			
			pstmt.setInt(4, bean.getExpire());
			pstmt.setInt(5, bean.getId());

			int i = pstmt.executeUpdate();
			if (i == 1) {
				result = this.select(bean.getId());
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return result;
	}

	private static final String DELETE = "delete from product where id=?";
	public int delete(int id) {
		int result = 0;
		Connection conn = null;
		PreparedStatement pstmt = null;

		try {
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(DELETE);
			pstmt.setInt(1, id);
			result = pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
	SimpleDateFormat sdf = new SimpleDateFormat("yyyy-mm-dd");
	private static final String SELECT_ALL = "select ID , NAME , PRICE , MAKE , EXPIRE from product";
	public List<String[]> select() {
		List<String[]> result = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(SELECT_ALL);
			rs = pstmt.executeQuery();
			int cols = rs.getMetaData().getColumnCount();
			result = new ArrayList<String[]>();
			while(rs.next()){
				String[] tmp = new String[cols];
				tmp[0]=""+rs.getInt("ID");
				tmp[1]=rs.getString("NAME")==null?null:rs.getString("NAME");
				tmp[2]=""+rs.getDouble("PRICE");
				tmp[3] = rs.getDate("MAKE")==null?null:sdf.format(rs.getDate("MAKE"));
				tmp[4]=""+rs.getInt("EXPIRE");
				result.add(tmp);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (rs!=null) {
				try {
					rs.close();
				} catch (SQLException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				}
			}
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

		return result;
	}
	private static final String SELECT_ALL_BY_NAME = "select ID , NAME , PRICE , MAKE , EXPIRE from product where NAME like ?";
	public List<String[]> select(String name) {
		List<String[]> result = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(SELECT_ALL_BY_NAME);
			pstmt.setString(1, "%"+name+"%");
			rs = pstmt.executeQuery();
			int cols = rs.getMetaData().getColumnCount();
			result = new ArrayList<String[]>();
			while(rs.next()){
				String[] tmp = new String[cols];
				tmp[0]=""+rs.getInt("ID");
				tmp[1]=rs.getString("NAME")==null?null:rs.getString("NAME");
				tmp[2]=""+rs.getDouble("PRICE");
				tmp[3] = rs.getDate("MAKE")==null?null:sdf.format(rs.getDate("MAKE"));
				tmp[4]=""+rs.getInt("EXPIRE");
				result.add(tmp);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (rs!=null) {
				try {
					rs.close();
				} catch (SQLException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				}
			}
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

		return result;
	}
}
